from flask import request, jsonify

from app.api import zhaosheng
from app.setting import DevConfig

engine = DevConfig.engine



@zhaosheng.route('/')
def hello_word():
    return "<h1>hello_word</h1>"


@zhaosheng.route('/studentNum4Country')
def chinaCountry():
    # 学院全国招生情况（分年度 招生人数、报到人数）
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        print("拿到的年为", year)
        # year分为2018和2019
        sql = "select name,value,actual,ROUND(cast(actual as float)/value,2)*100 as rate from  " \
              "(select chinaProvince as name,count(*)as value,sum(case when reported='已报道' then  1 else 0 end) as actual " \
              "from studentdata where year='%s' and chinaProvince !='' group by chinaProvince) t1;" % year
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            actual = str(data[2])
            rate = str(data[3])
            jsondata = {
                "value": value,
                "name": name,
                "actual": actual,
                "rate": rate
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/studentNum4Guangdong')
def guangdongCountry():
    # 学院广东省各地级市招生情况（分地级市 招生人数、报到人数）
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        print("拿到的年为", year)
        # year分为2018和2019
        sql = "select name,value,actual,ROUND(actual/value,2)*100 as rate from " \
              " (select guangdongProvince as name,count(*)as value,sum(case when reported='已报道' then  1 else 0 end) as actual" \
              " from studentdata where year='%s' and guangdongProvince !='' and chinaProvince='广东' group by guangdongProvince) t1;" % year
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            actual = str(data[2])
            rate = str(data[3])
            jsondata = {
                "value": value,
                "name": name,
                "actual": actual,
                "rate": rate
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/checkin')
def checkin():
    # 考生报到情况
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = ' select reported,count(*) from studentdata where year="{}" group by reported;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            if name == "已报道":
                name = "报到"
            else:
                name = "流失"
            jsondata = {
                "name": name,
                "value": value
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/studentSex')
def studentSex():
    # 考生分布
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = 'select sex, count(*) as count_sex from studentdata where year="{}" and sex!="" group by sex;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            if name == "男":
                name = "男性"
            else:
                name = "女性"
            jsondata = {
                "value": value,
                "name": name
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/studentSubject')
def studentSubject():
    # 文理科人数比例
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = ' select recruitType,count(*) as counts from studentdata where recruitType="普高文科" and year="{}" or recruitType="普高理科" and year="{}" group by recruitType;'.format(
            year, year)
        datas = engine.execute(sql)
        app = []

        for data in datas:
            name = data[0]
            value = data[1]
            if name == "普高理科":
                name = "理科"
            else:
                name = "文科"
            jsondata = {
                "value": value,
                "name": name
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/recruit4Institute')
def recruit4Institute():
    # 各院系招生人数及报到率
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = 'select type,num,ROUND(cast(actual as float)/num,2)*100 as rate from(select college as type,count(*) as num,sum(case when reported="已报道" then 1 else 0 end)as actual  from studentdata where year="{}" group by college) a1;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            type = data[0]
            num = data[1]
            rate = str(data[2])
            jsondata = {
                "num": num,
                "type": type,
                "rate": rate
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/recruit4Major')
def recruit4Major():
    # 各专业招生人数及报到率
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = 'select type,num,ROUND(cast(actual as float)/num,2)*100 as rate from(select  professional as type,count(*) as num,sum(case when reported="已报道" then 1 else 0 end)as actual  from studentdata where year="{}" group by  professional) a1;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            type = data[0]
            num = data[1]
            rate = str(data[2])
            jsondata = {
                "num": num,
                "type": type,
                "rate": rate
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/recruitNumTop4Institute')
def recruitNumTop10Institute():
    # 各院系招生人数排名前10
    if request.method == 'GET':
        sql = 'select college,count(*) as counts from studentdata  group by college order by counts desc limit 10;'
        datas = engine.execute(sql)
        app = []
        for data in datas:
            college = data[0]
            num = data[1]
            jsondata = {
                "num": num,
                "type": college,
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/recruitRateTop4Institute')
def recruitRateTop10Institute():
    # 各院系报到率排名 前10
    if request.method == 'GET':
        sql = 'select type,ROUND(cast(actual as float)/num,2)*100 as rate from(select  college as type,count(*) as num,sum(case when reported="已报道" then 1 else 0 end)as actual  from studentdata  group by  college) a1 order by rate desc limit 10;'
        datas = engine.execute(sql)
        app = []
        for data in datas:
            types = data[0]
            rate = str(data[1])

            jsondata = {
                "num": rate,
                "type": types,
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/recruitNumTop4Major')
def recruitNumTop10Major():
    # 各专业招生人数排名前10
    if request.method == 'GET':
        sql = 'select professional,count(*) as counts from studentdata group by professional order by counts desc limit 10;'
        datas = engine.execute(sql)
        app = []
        for data in datas:
            professional = data[0]
            num = data[1]
            jsondata = {
                "num": num,
                "type": professional,
            }
            app.append(jsondata)
        return jsonify(app)


@zhaosheng.route('/recruitRateTop4Major')
def recruitRateTop10Major():
    # 各专业报到率排名前10
    if request.method == 'GET':
        sql = 'select type,ROUND(cast(actual as float)/num,2)*100 as rate from(select  professional as type,count(*) as num,sum(case when reported="已报道" then 1 else 0 end)as actual  from studentdata  group by  professional) a1 order by rate desc limit 10;'
        datas = engine.execute(sql)
        app = []
        for data in datas:
            types = data[0]
            rate = str(data[1])

            jsondata = {
                "num": rate,
                "type": types,
            }
            app.append(jsondata)
        return jsonify(app)


# 目前做不了
@zhaosheng.route('/regTop')
def regTop():
    # 各专业报考志愿排名及其百分比（分第一/二/三/四/五志愿 按总人数排名）
    pass
    return jsonify([])


@zhaosheng.route('/regNumTop4guangdong')
def regNumTop10guangdong():
    # 广东省各地区报考人数排名
    sql = "select name,value from " \
          " (select guangdongProvince as name,count(*)as value,sum(case when reported='已报道' then  1 else 0 end) as actual" \
          " from studentdata where  guangdongProvince !='' and chinaProvince='广东' group by guangdongProvince) t1 order by value desc ;"
    datas = engine.execute(sql)
    app = []
    for data in datas:
        types = data[0]
        rate = str(data[1])

        jsondata = {
            "num": rate,
            "type": types,
        }
        app.append(jsondata)
    return jsonify(app)


@zhaosheng.route('/regRateTop4guangdong')
def regRateTop4guangdong():
    # 广东省各地区报到率排名
    sql = "select name,ROUND(cast(actual as float)/value,2)*100 as rate from (select guangdongProvince as name,count(*)as value,sum(case when reported='已报道' then  1 else 0 end) as actual from studentdata where  guangdongProvince !='' and chinaProvince='广东' group by guangdongProvince) t1 order by rate desc ;"
    datas = engine.execute(sql)
    app = []
    for data in datas:
        types = data[0]
        rate = str(data[1])

        jsondata = {
            "num": rate,
            "type": types,
        }
        app.append(jsondata)
    return jsonify(app)


@zhaosheng.route('/regNumTop4Province')
def regNumTop4Province():
    # 外省报考人数排名
    sql = "select name,value from " \
          " (select chinaProvince as name,count(*)as value,sum(case when reported='已报道' then  1 else 0 end) as actual" \
          " from studentdata where chinaProvince!='' and chinaProvince!='广东' group by chinaProvince) t1 order by value desc ;"
    datas = engine.execute(sql)
    app = []
    for data in datas:
        types = data[0]
        rate = str(data[1])

        jsondata = {
            "num": rate,
            "type": types,
        }
        app.append(jsondata)
    return jsonify(app)


@zhaosheng.route('/regRateTop4Province')
def regRateTop4Province():
    # 外省报考报到率排名
    sql = "select name,ROUND(cast(actual as float )/value,2)*100 as rate from " \
          " (select chinaProvince as name,count(*)as value,sum(case when reported='已报道' then  1 else 0 end) as actual" \
          " from studentdata where chinaProvince!='' and chinaProvince!='广东' group by chinaProvince) t1 order by rate desc ;"
    datas = engine.execute(sql)
    app = []
    for data in datas:
        types = data[0]
        rate = str(data[1])

        jsondata = {
            "num": rate,
            "type": types,
        }
        app.append(jsondata)
    return jsonify(app)

@zhaosheng.route('/suggest')
def suggestType():
    # 以下院系招生报到率高，建议加大招生额度（报到率前 8）
    if request.method == 'GET':
        formdata = request.args
        # types 为字符串
        types = formdata['type']
        # 要根据大屏幕的展示的效果进一步判断，目前是专业
        if types == '0':

            sql = "select major,num,actual,ROUND(cast(actual as float)/num,2)*100 as rate from " \
                  " (select  professional as major,count(*)as num,sum(case when reported='已报道' then  1 else 0 end) " \
                  "as actual from studentdata where  professional !=''  group by professional) t1 order by rate desc limit 8;"
            datas = engine.execute(sql)
            app = []
            for data in datas:
                major = data[0]
                num = data[1]
                jsondata = {
                    "major": major,
                    "num": num
                }
                app.append(jsondata)
            return jsonify(app)
        elif types == '1':
            sql = "select province,num,actual,ROUND(cast(actual as float)/num,2)*100 as rate from " \
                  " (select  chinaProvince as province,count(*)as num,sum(case when reported='已报道' then  1 else 0 end) " \
                  "as actual from studentdata where  chinaProvince !=''  group by chinaProvince) t1 order by rate desc limit 8;"
            datas = engine.execute(sql)
            app = []
            for data in datas:
                province = data[0]
                num = data[1]
                jsondata = {
                    "province": province,
                    "num": num
                }
                app.append(jsondata)
            return jsonify(app)

        elif types == '2':
            sql = "select major,num,actual,ROUND(cast(actual as float)/num,2)*100 as rate from " \
                  " (select  professional as major,count(*)as num,sum(case when reported='已报道' then  1 else 0 end)" \
                  " as actual from studentdata where  professional !=''  group by professional) t1 order by rate desc limit 9;"
            datas = engine.execute(sql)
            app = []
            for data in datas:
                major = data[0]
                rate = str(data[3])
                jsondata = {
                    "major": major,
                    "rate": rate
                }
                app.append(jsondata)
            return jsonify(app)
        elif types == '3':
            sql = "select city,num,actual,ROUND(cast(actual as float)/num,2)*100 as rate from " \
                  " (select guangdongProvince as city,count(*)as num," \
                  "sum(case when reported='已报道' then  1 else 0 end) as actual from studentdata where  " \
                  "guangdongProvince !='' and chinaProvince='广东' group by guangdongProvince) t1 order by rate limit 8;"
            datas = engine.execute(sql)
            app = []
            for data in datas:
                city = data[0]
                rate = str(data[3])
                jsondata = {
                    "city": city,
                    "rate": rate
                }
                app.append(jsondata)
            return jsonify(app)
        else:
            return "args error"
    else:
        return "args error"


# 目前做不了
'''
响应数据中 type 代表分数段 请求参数 subject 代表文理科（目前有 w、l 两种 参数）type 代表数据范围（目前有 school 全校范围 major 专业范围 institute 学院范 围）
'''
@zhaosheng.route('/quality?subject=w&type=school')
def qulitySubjectType():
    booklib = [
        {type: '300-350','num': 256},
        {type: '350-400','num': 318},
        {type: '400-450','num': 249},
        {type: '500-550','num': 136},
        ]
    return jsonify(booklib)


# 目前做不了
@zhaosheng.route('/quality')
def qulitySubjectTypeInstitute():
    # 各学院广东生源质量分析 （分学院 分文理科）
   student_need= [
       {'type': '第一志愿', 'num': 400},
       {'type': '第二志愿', 'num': 480},
       {'type': '第三志愿', 'num': 720},
       {'type': '第四志愿', 'num': 630},
        {'type': '第五志愿', 'num': 520}]
   return jsonify(student_need)
